using System;
using System.Data;
using System.Data.Common;
using System.Text.RegularExpressions;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.ExceptionHandling;

namespace Ks.Fs.Core.Data.SqlDataProvider
{
    public class SqlHelper
    {
        #region Private Fields

        private DbConnection _connection;
        private Database _database;
        private DbTransaction _transaction;

        #endregion

        #region Constructors

        public SqlHelper()
        {
            _database = DatabaseFactory.CreateDatabase();
            _transaction = null;
        }

        public SqlHelper(string connectionStringName)
        {
            _database = DatabaseFactory.CreateDatabase(connectionStringName);
            _transaction = null;
        }

        #endregion

        #region Public Methods

        /// <summary>
        /// Execute a stored procedure and return a scalar value
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public object ExecuteScalar(string storedProcedureName,
                                    params object[] parameterValues)
        {
            DbCommand command = null;
            object returnValue = null;

            command = _database.GetStoredProcCommand(storedProcedureName, parameterValues);

            try
            {
                if (_connection == null)
                {
                    returnValue = _database.ExecuteScalar(command);
                }
                else
                {
                    if (_transaction == null)
                    {
                        command.Connection = _connection;
                        returnValue = command.ExecuteScalar();
                    }
                    else
                    {
                        returnValue = _database.ExecuteScalar(command, _transaction);
                    }
                }
            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, "Data Access Policy");

                if (rethrow)
                {
                    // Rethrow exception.
                    throw ex;
                }
            }

            return returnValue;
        }

        /// <summary>
        /// Execute a nonquery command
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string storedProcedureName,
                                   params object[] parameterValues)
        {
            DbCommand command = null;
            int rowsAffected = -1;

            command = _database.GetStoredProcCommand(storedProcedureName, parameterValues);

            try
            {
                if (_connection == null)
                {
                    rowsAffected = _database.ExecuteNonQuery(command);
                }
                else
                {
                    if (_transaction == null)
                    {
                        command.Connection = _connection;
                        command.ExecuteNonQuery();
                    }
                    else
                    {
                        rowsAffected = _database.ExecuteNonQuery(command, _transaction);
                    }
                }
            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, "Data Access Policy");

                if (rethrow)
                {
                    // Rethrow exception.
                    throw ex;
                }
            }

            return rowsAffected;
        }

        /// <summary>
        /// Execute query string command
        /// </summary>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        public void ExecuteSqlString(string sqlString)
        {
            string[] sqls = Regex.Split(sqlString, "GO\r\n", RegexOptions.IgnoreCase | RegexOptions.Multiline);
            try
            {
                foreach (string sql in sqls)
                {
                    DbCommand command = _database.GetSqlStringCommand(sql);

                    if (_connection == null)
                    {
                        _database.ExecuteNonQuery(command);
                    }
                    else
                    {
                        if (_transaction == null)
                        {
                            command.Connection = _connection;
                            command.ExecuteNonQuery();
                        }
                        else
                        {
                            _database.ExecuteNonQuery(command, _transaction);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, "Data Access Policy");
                if (rethrow)
                {
                    // Rethrow exception.
                    throw ex;
                }
            }
        }

        /// <summary>
        /// Execute a nonquery command and assign an out parameter value
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="outParameterName"></param>
        /// <param name="outParameterValue"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public int ExecuteNonQueryWithOutParameter(string storedProcedureName,
                                                   string outParameterName,
                                                   out object outParameterValue,
                                                   params object[] parameterValues)
        {
            DbCommand command = null;
            int rowsAffected = -1;

            outParameterValue = null;

            command = _database.GetStoredProcCommand(storedProcedureName, parameterValues);

            try
            {
                if (_connection == null)
                {
                    rowsAffected = _database.ExecuteNonQuery(command);
                }
                else
                {
                    if (_transaction == null)
                    {
                        command.Connection = _connection;
                        command.ExecuteNonQuery();
                    }
                    else
                    {
                        rowsAffected = _database.ExecuteNonQuery(command, _transaction);
                    }
                }
                outParameterValue = _database.GetParameterValue(command, outParameterName);
            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, "Data Access Policy");

                if (rethrow)
                {
                    // Rethrow exception.
                    throw ex;
                }
            }

            return rowsAffected;
        }

        /// <summary>
        /// Execute a stored procedure and return a data reader
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string storedProcedureName,
                                         params object[] parameterValues)
        {
            DbCommand command = null;
            IDataReader reader = null;

            command = _database.GetStoredProcCommand(storedProcedureName, parameterValues);

            try
            {
                if (_connection == null)
                {
                    reader = _database.ExecuteReader(command);
                }
                else
                {
                    if (_transaction == null)
                    {
                        command.Connection = _connection;
                        reader = command.ExecuteReader();
                    }
                    else
                    {
                        reader = _database.ExecuteReader(command, _transaction);
                    }
                }
            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, "Data Access Policy");

                if (rethrow)
                {
                    // Rethrow exception.
                    throw ex;
                }
            }

            return reader;
        }

        /// <summary>
        /// Execute a stored procedure and return a data set
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string storedProcedureName,
                                         params object[] parameterValues)
        {
            DbCommand command;
            DataSet ds = null;

            command = _database.GetStoredProcCommand(storedProcedureName);

            try
            {
                if (_connection == null)
                {
                    ds = _database.ExecuteDataSet(command);
                }
                else
                {
                    if (_transaction == null)
                    {
                        command.Connection = _connection;
                        ds = _database.ExecuteDataSet(command, null);
                    }
                    else
                    {
                        ds = _database.ExecuteDataSet(command, _transaction);
                    }
                }
            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, "Data Access Policy");

                if (rethrow)
                {
                    // Rethrow exception.
                    throw ex;
                }
            }

            return ds;
        }

        #region Transaction Manipulation Methods

        /// <summary>
        /// Begin a transaction
        /// </summary>
        public void BeginTransaction()
        {
            _connection = _database.CreateConnection();
            _connection.Open();
            _transaction = _connection.BeginTransaction();
        }

        /// <summary>
        /// Commit transaction
        /// </summary>
        public void Commit()
        {
            if (_transaction != null)
            {
                try
                {
                    _transaction.Commit();
                }
                catch (Exception ex)
                {
                    bool rethrow = ExceptionPolicy.HandleException(ex, "Data Access Policy");

                    if (rethrow)
                    {
                        // Rethrow exception.
                        throw ex;
                    }
                }
                finally
                {
                    if (_connection != null)
                    {
                        _connection.Close();
                    }

                    _transaction = null;
                }
            }
        }

        /// <summary>
        /// Rollback transaction
        /// </summary>
        public void Rollback()
        {
            if (_transaction != null)
            {
                _transaction.Rollback();

                if (_connection != null)
                {
                    _connection.Close();
                }

                _transaction = null;
            }
        }

        #endregion

        #region Connection Manipulation Methods

        public void OpenConnection()
        {
            if (_connection == null)
            {
                _connection = _database.CreateConnection();
                _connection.Open();
            }
        }

        public void CloseConnection()
        {
            if (_connection == null)
            {
                return;
            }
            if (_connection.State != ConnectionState.Closed)
            {
                _connection.Close();
            }
            _connection = null;
        }

        #endregion

        #endregion
    }
}
